/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitoring.xlspersistence;

import com.smartxls.WorkBook;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import monitoring.beans.XLS.MonitoringXLS;
import monitoring.beans.database.MonitoringDB;
import monitoring.beans.util.FileBean;
import monitoring.beans.util.ImportMessage;
import monitoring.mapping.XLStoDB;
import monitoring.ws.ExportMessage;
import org.joda.time.field.ImpreciseDateTimeField;

/**
 *
 * @author Regulus
 */
public class MonitoringXLSPersistence {

    private List<ImportMessage> msgs;

    public MonitoringXLSPersistence() {
        this.msgs = new ArrayList<ImportMessage>();
    }

    public WorkBook readFile() throws Exception {
        WorkBook workBook = new WorkBook();
        workBook.readXLSX("PanTrap_Ver01.xlsx");//"PanTrap_Ver01.xlsx");
        return workBook;
    }

    public WorkBook readFile(String path) throws Exception {
        WorkBook workBook = new WorkBook();
        workBook.readXLSX(path);
        return workBook;
    }

    public ExportMessage persist(List<MonitoringXLS> listxls) throws Exception {
        WorkBook workBook = readFile();
        writeLines(listxls, workBook);

        String path = "/mnt/hd/tmp/";
        Date d = new Date();
        int year = d.getYear();
        int month = d.getMonth();
        int day = d.getDay();
        int hour = d.getHours();
        int min = d.getMinutes();
        int sec = d.getSeconds();
        
//        Calendar cal = Calendar.getInstance();  
//        int week = cal.getWeekYear();
//        Date time = cal.getTime();
//             int miliSec = (int) cal.getTimeInMillis();
        String fileName = year+"_"+month+"_"+day+"_"+hour+"_"+min+"_"+sec;
        workBook.writeXLSX(path+fileName+".xlsx");        
        ExportMessage expo = new ExportMessage();
        expo.setPath("/tmp/"+fileName+".xlsx");
        expo.setLines(listxls.size());
        
        
        return expo;
    }

    private void writeLines(List<MonitoringXLS> listxls, WorkBook workBook) throws Exception {
        workBook.setText("B1", listxls.get(0).getDenomination());
        for (int k = 0; k < listxls.size(); k++) {
            workBook.setEntry(k + 4, 0, listxls.get(k).getCollectionTechinique());
            workBook.setEntry(k + 4, 1, listxls.get(k).getIdgeral() != null ? listxls.get(k).getIdgeral().toString() : null);
            workBook.setEntry(k + 4, 2, listxls.get(k).getCollector());
            workBook.setEntry(k + 4, 3, listxls.get(k).getDigitizer());
            workBook.setEntry(k + 4, 4, listxls.get(k).getCountry());
            workBook.setEntry(k + 4, 5, listxls.get(k).getState());
            workBook.setEntry(k + 4, 6, listxls.get(k).getCity());
            workBook.setEntry(k + 4, 7, listxls.get(k).getLocation());
            workBook.setEntry(k + 4, 8, listxls.get(k).getComplementaryDataOfLocation());
            workBook.setEntry(k + 4, 9, listxls.get(k).getGeodeticDatum());
            workBook.setEntry(k + 4, 10, listxls.get(k).getLatitude() != null ? listxls.get(k).getLatitude().toString() : null);
            workBook.setEntry(k + 4, 11, listxls.get(k).getLongitude() != null ? listxls.get(k).getLongitude().toString() : null);
            workBook.setEntry(k + 4, 12, listxls.get(k).getAltitude() != null ? listxls.get(k).getAltitude().toString() : null);
            workBook.setEntry(k + 4, 13, listxls.get(k).getGpsPrecision());
            workBook.setEntry(k + 4, 14, listxls.get(k).getGpsReferencePoints());
            workBook.setEntry(k + 4, 15, listxls.get(k).getCulture());
            workBook.setEntry(k + 4, 16, listxls.get(k).getCultivate());
            workBook.setEntry(k + 4, 17, listxls.get(k).getSurroundingsVegetation());
            workBook.setEntry(k + 4, 18, listxls.get(k).getPredominatBiome());
            
            SimpleDateFormat fDate = new SimpleDateFormat("dd/MM/yyyy");
            
            SimpleDateFormat fTime = new SimpleDateFormat("HH:mm");
            

            workBook.setEntry(k + 4, 19, listxls.get(k).getInstalationDate() != null ? fDate.format(listxls.get(k).getInstalationDate()) : null);
            workBook.setEntry(k + 4, 20, listxls.get(k).getInstalationTime() != null ? fTime.format(listxls.get(k).getInstalationTime()) : null);
            workBook.setEntry(k + 4, 21, listxls.get(k).getCollectionDate() != null ? fDate.format(listxls.get(k).getCollectionDate()) : null);
            workBook.setEntry(k + 4, 22, listxls.get(k).getCollectionTime() != null ? fTime.format(listxls.get(k).getCollectionTime()) : null);
            workBook.setEntry(k + 4, 23, listxls.get(k).getSurroundingsCulture());
            workBook.setEntry(k + 4, 24, listxls.get(k).getPlotNumber() != null ? listxls.get(k).getPlotNumber().toString() : null);
            workBook.setEntry(k + 4, 25, listxls.get(k).getAmostralNumber() != null ? listxls.get(k).getAmostralNumber().toString() : null);
            workBook.setEntry(k + 4, 26, listxls.get(k).getPanTrapColor());
            workBook.setEntry(k + 4, 27, listxls.get(k).getSupporttype());
            workBook.setEntry(k + 4, 28, listxls.get(k).getFloorHeight() != null ? listxls.get(k).getFloorHeight().toString() : null);
            workBook.setEntry(k + 4, 29, listxls.get(k).getOrder());
            workBook.setEntry(k + 4, 30, listxls.get(k).getPopularName());
            workBook.setEntry(k + 4, 31, listxls.get(k).getFamily());
            workBook.setEntry(k + 4, 32, listxls.get(k).getTribe());
            workBook.setEntry(k + 4, 33, listxls.get(k).getSubTribe());
            workBook.setEntry(k + 4, 34, listxls.get(k).getGenus());
            workBook.setEntry(k + 4, 35, listxls.get(k).getSpecies());
            workBook.setEntry(k + 4, 36, listxls.get(k).getSubSpecies());
            workBook.setEntry(k + 4, 37, listxls.get(k).getScientificName());
            workBook.setEntry(k + 4, 38, listxls.get(k).getSex());
            workBook.setEntry(k + 4, 39, listxls.get(k).getWeight() != null ? listxls.get(k).getWeight().toString() : null);
            workBook.setEntry(k + 4, 40, listxls.get(k).getLcaDimensions());
            workBook.setEntry(k + 4, 41, listxls.get(k).getInstitutionCode());
            workBook.setEntry(k + 4, 42, listxls.get(k).getCollectionCode());
            workBook.setEntry(k + 4, 43, listxls.get(k).getCollectionName());
            workBook.setEntry(k + 4, 44, listxls.get(k).getCatalogNumber());
        }
    }

    public MonitoringXLS readLine(WorkBook w, int k) throws Exception {
        MonitoringXLS m = new MonitoringXLS();
        m.setCollectionTechinique(w.getText(k, 0));
        m.setIdgeral(w.getText(k, 1));
        m.setCollector(w.getText(k, 2));
        m.setDigitizer(w.getText(k, 3));
        m.setCountry(w.getText(k, 4));
        m.setState(w.getText(k, 5));
        m.setCity(w.getText(k, 6));
        m.setLocation(w.getText(k, 7));
        m.setComplementaryDataOfLocation(w.getText(k, 8));
        m.setGeodeticDatum(w.getText(k, 9));
        try {
            if (!w.getText(k, 10).trim().equals("")) {
                m.setLatitude(BigDecimal.valueOf(w.getNumber(k, 10)));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Latitude");
            msgs.add(msg);
            m.setLatitude(null);
        }
        try {
            if (!w.getText(k, 11).trim().equals("")) {
                m.setLongitude(BigDecimal.valueOf(w.getNumber(k, 11)));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Longitude");
            msgs.add(msg);
            m.setLongitude(null);
        }
        try {
            if (!w.getText(k, 12).trim().equals("")) {
                m.setAltitude(BigDecimal.valueOf(w.getNumber(k, 12)));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Altitude");
            msgs.add(msg);
            m.setAltitude(null);
        }
        m.setGpsPrecision(w.getText(k, 13));
        m.setGpsReferencePoints(w.getText(k, 14));
        m.setCulture(w.getText(k, 15));
        m.setCultivate(w.getText(k, 16));
        m.setSurroundingsVegetation(w.getFormattedText(k, 17));
        m.setPredominatBiome(w.getText(k, 18));

        SimpleDateFormat fDate = new SimpleDateFormat("dd/MM/yyyy");

        try {
            if (!w.getFormattedText(k, 19).trim().equals("")) {
                m.setInstalationDate(fDate.parse(w.getFormattedText(k, 19).trim()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato de data incorreto (Ex.: 24/5/2012).", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Data da Instalação");
            msgs.add(msg);
            m.setInstalationDate(null);
        }
        SimpleDateFormat fTime = new SimpleDateFormat("HH:mm");
        try {
            if (!w.getFormattedText(k, 20).trim().equals("")) {
                m.setInstalationTime(new Timestamp(fTime.parse(w.getFormattedText(k, 20).trim()).getTime()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato de horário incorreto (Ex.: 16:45).", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Hora da Instalação");
            msgs.add(msg);
            m.setInstalationTime(null);
        }
        try {
            if (!w.getFormattedText(k, 21).trim().equals("")) {
                m.setCollectionDate(fDate.parse(w.getFormattedText(k, 21).trim()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato de data incorreto (Ex.: 24/5/2012).", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Data da Coleta");
            msgs.add(msg);
            m.setCollectionDate(null);
        }
        try {
            if (!w.getFormattedText(k, 22).trim().equals("")) {
                m.setCollectionTime(new Timestamp(fTime.parse(w.getFormattedText(k, 22).trim()).getTime()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato de horário incorreto (Ex.: 16:45).", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Hora da Coleta");
            msgs.add(msg);
            m.setCollectionTime(null);
        }
        m.setSurroundingsCulture(w.getFormattedText(k, 23));
        try {
            if (!w.getText(k, 24).trim().equals("")) {
                m.setPlotNumber(Integer.parseInt(w.getText(k, 24).trim()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto.", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Número do Plot");
            msgs.add(msg);
            m.setPlotNumber(null);
        }
        try {
            if (!w.getText(k, 25).trim().equals("")) {
                m.setAmostralNumber(Integer.parseInt(w.getText(k, 25).trim()));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto.", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Unidades Amostrais");
            msgs.add(msg);
            m.setAmostralNumber(null);
        }
        m.setPanTrapColor(w.getText(k, 26));
        m.setSupporttype(w.getFormattedText(k, 27));
        try {
            if (!w.getText(k, 28).trim().equals("")) {
                m.setFloorHeight(BigDecimal.valueOf(w.getNumber(k, 28)));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto.", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Altura do chão");
            msgs.add(msg);
            m.setFloorHeight(null);
        }
        m.setOrder(w.getText(k, 29));
        m.setPopularName(w.getText(k, 30));
        m.setFamily(w.getText(k, 31));
        m.setTribe(w.getText(k, 32));
        m.setSubTribe(w.getText(k, 33));
        m.setGenus(w.getText(k, 34));
        m.setSpecies(w.getText(k, 35));
        m.setSubSpecies(w.getText(k, 36));
        m.setScientificName(w.getText(k, 37));
        m.setSex(w.getFormattedText(k, 38));
        try {
            if (!w.getText(k, 39).trim().equals("")) {
                m.setWeight(BigDecimal.valueOf(w.getNumber(k, 39)));
            }
        } catch (Exception e) {
            ImportMessage msg = new ImportMessage(ImportMessage.TYPE_WARNING, "Formato numérico incorreto.", ImportMessage.IMPL_FIELD_OMIT, k + 1, "Peso");
            msgs.add(msg);
            m.setWeight(null);
        }
        m.setLcaDimensions(w.getText(k, 40));
        m.setInstitutionCode(w.getText(k, 41));
        m.setCollectionCode(w.getText(k, 42));
        m.setCollectionName(w.getText(k, 43));
        m.setCatalogNumber(w.getText(k, 44));

        return m;
    }

    public boolean isEmpty(WorkBook w, int k) throws Exception {
        for (int i = 0; i < 44; i++) {
            if (w.getText(k, i).trim().length() > 1) {
                return false;
            }
        }
        return true;
    }

    public List<ImportMessage> getMsgs() {
        return msgs;
    }

    public void setMsgs(List<ImportMessage> msgs) {
        this.msgs = msgs;
    }
}
